﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MJM.RPP.EntidadesNegocio;
using System.Data;
using System.Data.SqlClient;

namespace MJM.RPP.AccesoDatos
{
    public class ContriPerAD
    {
        public int Insertar(ContriPerEN obj)
        {
            int codigo = 0;
            SqlConnection conn = new SqlConnection(MJM.CBD.Conn.Cadena.GetCadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_i_T_WEB_PERCON_Registrar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmUsuario, prmNroDoc, prmCodContri, prmCorreo, prmPass, prmDirFis,prmID,prmNombre;

            try
            {
                prmUsuario = cmd.CreateParameter();
                prmUsuario.ParameterName = "@vUSUPAD_P_chUSU";
                prmUsuario.SqlDbType = SqlDbType.VarChar;
                prmUsuario.Value = obj.USUPAD_P_chUSU;
                cmd.Parameters.Add(prmUsuario);

                prmNroDoc = cmd.CreateParameter();
                prmNroDoc.ParameterName = "@vPERCON_chNRODOC";
                prmNroDoc.SqlDbType = SqlDbType.VarChar;
                prmNroDoc.Value = obj.PERCON_chNRODOC;
                cmd.Parameters.Add(prmNroDoc);

                prmCodContri = cmd.CreateParameter();
                prmCodContri.ParameterName = "@vPERCON_chCODCONT";
                prmCodContri.SqlDbType = SqlDbType.Int;
                prmCodContri.Value = obj.PERCON_chCODCONT;
                cmd.Parameters.Add(prmCodContri);

                prmCorreo = cmd.CreateParameter();
                prmCorreo.ParameterName = "@vPERCON_chCORCON";
                prmCorreo.SqlDbType = SqlDbType.VarChar;
                prmCorreo.Value = obj.PERCON_chCORCON;
                cmd.Parameters.Add(prmCorreo);

                prmPass = cmd.CreateParameter();
                prmPass.ParameterName = "@vPERCON_chPASSWEB";
                prmPass.SqlDbType = SqlDbType.VarChar;
                prmPass.Value = obj.PERCON_chPASSWEB;
                cmd.Parameters.Add(prmPass);

                prmNombre = cmd.CreateParameter();
                prmNombre.ParameterName = "@vPERCON_chNOMCON";
                prmNombre.SqlDbType = SqlDbType.VarChar;
                prmNombre.Value = obj.PERCON_chNOMCON;
                cmd.Parameters.Add(prmNombre);

                prmDirFis = cmd.CreateParameter();
                prmDirFis.ParameterName = "@vPERCON_chDIRFIS";
                prmDirFis.SqlDbType = SqlDbType.VarChar;
                prmDirFis.Value = obj.PERCON_chDIRFIS;
                cmd.Parameters.Add(prmDirFis);

                prmID = cmd.CreateParameter();
                prmID.SqlDbType = SqlDbType.Int;
                prmID.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(prmID);

                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                codigo = Convert.ToInt32(prmID.Value);

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                codigo = 0;
                cmd.Dispose();
                conn.Dispose();
            }

            return codigo;

        }

        public ContriPerEN ObtenerSatmun(String sDNI)
        { 
            SqlConnection conn = new SqlConnection(MJM.CBD.Conn.Cadena.GetCadenaSatmun());
            SqlCommand cmd = new SqlCommand("S_S_V_PERS_PASS_VALID", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmCodigoContri;
            SqlDataReader dr;
            ContriPerEN obj=new ContriPerEN();
            int CodigoContri = ObtenerCodigoContri(sDNI);
            try
            {
                prmCodigoContri = cmd.CreateParameter();
                prmCodigoContri.ParameterName = "@iPERS_P_inCODPER";
                prmCodigoContri.SqlDbType = SqlDbType.Int;
                prmCodigoContri.Value = CodigoContri;
                cmd.Parameters.Add(prmCodigoContri);

                cmd.Connection.Open();
                dr=cmd.ExecuteReader();
                if (dr.Read())
                {
                    obj.PERCON_chCODCONT = dr.GetInt32(0).ToString();
                    obj.PERCON_chCORCON = dr.GetString(dr.GetOrdinal("REDO_chEMAIL"));
                    obj.PERCON_chNOMCON = dr.GetString(dr.GetOrdinal("PERS_chNOMCOM"));
                    obj.PERCON_chNRODOC = dr.GetString(dr.GetOrdinal("REDO_chNUMDNI")).Trim();
                    if (obj.PERCON_chNRODOC.Length < 8)
                        obj.PERCON_chNRODOC = dr.GetString(dr.GetOrdinal("REDO_chNUMRUC")).Trim();                     
                    obj.PERCON_chPASSWEB = dr.GetString(dr.GetOrdinal("REDO_chNRPASS"));
                    obj.PERCON_chDIRFIS = dr.GetString(dr.GetOrdinal("PERS_chPERDIR"));
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
                return null;
            }

            return obj;
        }

        public int ObtenerCodigoContri(String sDNI)
        {
            SqlConnection conn = new SqlConnection(MJM.CBD.Conn.Cadena.GetCadenaSatmun());
            SqlCommand cmd = new SqlCommand("s_s_M_PERS_NUM_DOC", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmCodigoContri;
            SqlDataReader dr;
            int codigo = 0;

            try
            {
                prmCodigoContri = cmd.CreateParameter();
                prmCodigoContri.ParameterName = "@lPERS_chNUMDOC";
                prmCodigoContri.SqlDbType = SqlDbType.VarChar;
                prmCodigoContri.Value = sDNI;
                cmd.Parameters.Add(prmCodigoContri);

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    codigo = dr.GetInt32(0);                    
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
                return 0;
            }
            return codigo;
        }

        public ContriPerEN Obtener(String Usuario)
        { 
            SqlConnection conn = new SqlConnection(MJM.CBD.Conn.Cadena.GetCadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_s_T_WEB_PERCON_Obtener", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmUsuario;
            SqlDataReader dr;
            ContriPerEN obj=new ContriPerEN();

            try
            {
                prmUsuario = cmd.CreateParameter();
                prmUsuario.ParameterName = "@vUSUPAD_P_chUSU";
                prmUsuario.SqlDbType = SqlDbType.VarChar;
                prmUsuario.Value = Usuario;
                cmd.Parameters.Add(prmUsuario);

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    obj.PERCON_chCODCONT = dr.GetInt32(0).ToString();
                    obj.PERCON_chCORCON = dr.GetString(dr.GetOrdinal("PERCON_chCORCON"));
                    obj.PERCON_chNOMCON = dr.GetString(dr.GetOrdinal("PERCON_chNOMCON"));
                    obj.PERCON_chNRODOC = dr.GetString(dr.GetOrdinal("PERCON_chNRODOC"));
                    obj.PERCON_chPASSWEB = dr.GetString(dr.GetOrdinal("PERCON_chPASSWEB"));
                    obj.PERCON_chDIRFIS = dr.GetString(dr.GetOrdinal("PERCON_chDIRFIS"));
                    obj.PERCON_inFLAELI = dr.GetInt32(dr.GetOrdinal("PERCON_inFLAELI"));
                }
                else
                {
                    obj = null;
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
                return null;
            }

            return obj;
        }

        public int Actualizar(String Usuario,int tipo)
        {
            SqlConnection conn = new SqlConnection(MJM.CBD.Conn.Cadena.GetCadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_u_T_WEB_PERCON_Confirmar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmUsuario,prmTipo;

            try
            {
                prmUsuario = cmd.CreateParameter();
                prmUsuario.ParameterName = "@vUSUPAD_P_chUSU";
                prmUsuario.SqlDbType = SqlDbType.VarChar;
                prmUsuario.Value = Usuario;
                cmd.Parameters.Add(prmUsuario);

                prmTipo = cmd.CreateParameter();
                prmTipo.ParameterName = "@vTipo";
                prmTipo.SqlDbType = SqlDbType.Int;
                prmTipo.Value = tipo;
                cmd.Parameters.Add(prmTipo);

                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
                return 0;
            }

            return 1;
        }
    }
}
